﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using BackTask.Core.Util;
using BackTask.Model;
using Newtonsoft.Json;

namespace BackTask.SqlServer.SqlServerAccessor
{
    public class SqlServerConnection
    {
        private string connectionString;

        private static SqlConnection GetSqlConnection(string connectionString)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);

            return sqlConnection;
        }

        public int EnableCreated(string connectionString)
        {
            this.connectionString = connectionString;
            using SqlConnection sqlConnection = GetSqlConnection(connectionString);
            using SqlCommand sqlCommand = sqlConnection.CreateCommand();
            string sql = $@"
                IF NOT EXISTS (
                    SELECT  *
                    FROM
                        sysobjects
                    WHERE
                        id = OBJECT_ID(N'[BackTaskConfigurations]')
                )
                BEGIN 
                    CREATE TABLE [dbo].[BackTaskConfigurations]( 
                        [ID] bigint IDENTITY(1,1) NOT NULL 
                        ,[Configuration] [nvarchar](2048) NOT NULL DEFAULT('')
                        ,[Name] [NVARCHAR](50) NOT NULL DEFAULT('')
                        ,[GroupName] [NVARCHAR](50) NOT NULL DEFAULT('DEFAULT')
                        ,[CreateTime] [DATETIME] NOT NULL DEFAULT(GETDATE())
                        ,[UpdateTime] [DATETIME] NOT NULL DEFAULT(GETDATE())
                        CONSTRAINT [PK_BackTaskConfiguration] PRIMARY KEY CLUSTERED 
                        (
                            [ID] ASC
                        ) ON [PRIMARY]
                    )     
                    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Configuration' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackTaskConfigurations', @level2type=N'COLUMN',@level2name=N'Configuration'
                    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackTaskConfigurations', @level2type=N'COLUMN',@level2name=N'Name'
                    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'GroupName' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackTaskConfigurations', @level2type=N'COLUMN',@level2name=N'GroupName'
                    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'CreateTime' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackTaskConfigurations', @level2type=N'COLUMN',@level2name=N'CreateTime'
                    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'UpdateTime' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackTaskConfigurations', @level2type=N'COLUMN',@level2name=N'UpdateTime'
				END";

            sqlCommand.CommandText = sql;
            sqlCommand.CommandType = CommandType.Text;
            sqlConnection.Open();
            return sqlCommand.ExecuteNonQuery();
        }

        public IEnumerable<string> QueryConfiguration()
        {
            List<string> configurations = new List<string>();
            using SqlConnection sqlConnection = GetSqlConnection(connectionString);
            using SqlCommand sqlCommand = sqlConnection.CreateCommand();
            string sql = $@"SELECT Configuration FROM [dbo].[BackTaskConfigurations]";
            sqlCommand.CommandText = sql;
            sqlCommand.CommandType = CommandType.Text;
            sqlConnection.Open();
            SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
            while (sqlDataReader.Read())
            {
                var configuration = sqlDataReader[0].ToString();
                configurations.Add(configuration);
            }

            return configurations;
        }

        public int DeleteConfiguration(string name, string groupName)
        {
            using SqlConnection sqlConnection = GetSqlConnection(connectionString);
            using SqlCommand sqlCommand = sqlConnection.CreateCommand();
            string sql = $@"DELETE TOP(1) FROM [dbo].[BackTaskConfigurations] WHERE [Name] = @Name AND [GroupName] = @GroupName";
            sqlCommand.CommandText = sql;
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar) { SqlValue = name });
            sqlCommand.Parameters.Add(new SqlParameter("@GroupName", SqlDbType.NVarChar) { SqlValue = groupName });
            sqlConnection.Open();
            return sqlCommand.ExecuteNonQuery();
        }

        public void AddConfiguration(TaskJob job)
        {
            using SqlConnection sqlConnection = GetSqlConnection(connectionString);
            using SqlCommand sqlCommand = sqlConnection.CreateCommand();
            string name = job.Name;
            string groupName = job.GroupName;
            string sql = "INSERT INTO [dbo].[BackTaskConfigurations]([Name], [GroupName], [Configuration]) VALUES(@Name, @GroupName, @Configuration)";
            sqlCommand.CommandText = sql;
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar) { SqlValue = name });
            sqlCommand.Parameters.Add(new SqlParameter("@GroupName", SqlDbType.NVarChar) { SqlValue = groupName });
            sqlCommand.Parameters.Add(new SqlParameter("@Configuration", SqlDbType.NVarChar) { SqlValue = job.ToString() });
        }

        public void UpdateConfiguration(TaskJob job)
        {
            using SqlConnection sqlConnection = GetSqlConnection(connectionString);
            using SqlCommand sqlCommand = sqlConnection.CreateCommand();
            string name = job.Name;
            string groupName = job.GroupName;
            string sql = "UPDATE TOP(1) [dbo].[BackTaskConfigurations] SET [Configuration] = @Configuration, [UpdateTime] = GETDATE() WHERE Name = @Name AND GroupName = @GroupName";
            sqlCommand.CommandText = sql;
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar) { SqlValue = name });
            sqlCommand.Parameters.Add(new SqlParameter("@GroupName", SqlDbType.NVarChar) { SqlValue = groupName });
            sqlCommand.Parameters.Add(new SqlParameter("@Configuration", SqlDbType.NVarChar) { SqlValue = job.ToString() });
        }
    }
}
